<?php

/**
 * php-pdo操作mysql封装类
 * 连接模式 PDO
 * User: simon_min@aliyun.com
 * Date: 2016/11/15
 * Time: 11:36
 */
class PDOModel
{
    protected static $_db = null;   //静态属性，避免重复连接数据库
    protected $_dbType = 'mysql';
    protected $_pconnect = true;  //是否使用长连接
    protected $_host = 'localhost';
    protected $_port = '3306';
    protected $_user = 'root';
    protected $_password = 'root';
    protected $_dbName = null;  //数据库名
    protected $_lastSql = false; //最后一条sql语句
    protected $_where = '';
    protected $_order = '';
    protected $_limit = '';
    protected $_field = '*';
    protected $_clear = 0;  //状态，0表示查询条件干净，1表示查询条件污染
    protected $_trans = 0;  //事务指令数
    protected $_tbName = NULL;  //表名

    /**
     * 初始化数据库
     */
    public function __construct($_tbName = NULL)
    {
        class_exists('PDO') or die('PDO : class not exists.');
        $this->_tbName = is_null($_tbName) ? C('DB_PREFIX') . $this->_tbName : C('DB_PREFIX') . $_tbName;
        $this->_host = C('DB_HOST');
        $this->_port = C('DB_PORT');
        $this->_user = C('DB_USER');
        $this->_password = C('DB_PASSWORD');
        $this->_dbName = C('DB_DATABASE');
        //连接数据库
        if (is_null(self::$_db)) {
            $this->_connect();
        }
    }

    /**
     * 连接数据库
     */
    protected function _connect()
    {
        //$dsn = 'mysql:dbname=test;host=127.0.0.1;port=3306';
        $dsn = $this->_dbType . ':dbname=' . $this->_dbName . ';host=' . $this->_host . ';port=' . $this->_port;
        $options = $this->_pconnect ? array(PDO::ATTR_PERSISTENT => true) : array();
        try {
            $db = new PDO($dsn, $this->_user, $this->_password, $options);
            //设置如果sql语句执行错误则抛出异常，事务会自动回滚
            $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
            //禁用prepared statements的仿真效果(防SQL注入)
            $db->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
        } catch (PDOException $e) {
            die('Connection failed: ' . $e->getMessage());
        }
        $db->exec('SET NAMES utf8');
        self::$_db = $db;
    }

    /**
     * 字段和表名添加`符号
     * 保证指令中出现关键字不出错 针对mysql
     * @param $value
     * @return string
     */
    protected function _addChar($value)
    {
        if ('*' == $value || false !== strpos($value, '(') || false !== strpos($value, '.') || false !== strpos($value, '`')) {
            //如果包含*或者使用sql方法 则不作处理
        } elseif (false === strpos($value, '`')) {
            $value = '`' . trim($value) . '`';
        }
        return $value;
    }

    /**
     * 取得数据表的字段信息
     * @param $tbName 表名
     * @return array 字段信息列表
     */
    protected function _tbFields()
    {
        $sql = 'SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME="' . $this->_tbName . '" AND TABLE_SCHEMA="' . $this->_dbName . '"';
        $stmt = self::$_db->prepare($sql);
        $stmt->execute();
        $result = $stmt->fetchALL(PDO::FETCH_ASSOC);
        $res = array();
        foreach ($result as $Key => $value) {
            $res[$value['COLUMN_NAME']] = 1;
        }
        return $res;
    }

    /**
     * 过滤并格式化数据表字段
     * @param $data POST提交数据
     * @return array
     */
    protected function _dataFormat($data)
    {
        if (!is_array($data)) return array();
        $table_column = $this->_tbFields($this->_tbName);
        $res = array();
        foreach ($data as $k => $v) {
            if (!is_scalar($v)) continue;   //值不是标量则跳过
            if (array_key_exists($k, $table_column)) {
                $k = $this->_addChar($k);
                if (is_int($v)) {
                    $v = intval($v);
                } elseif (is_float($v)) {
                    $v = floatval($v);
                } elseif (preg_match('/^\(\w*(\+|\-|\*|\/)?\w*\)$/i', $v)) {
                    // 支持在字段的值里面直接使用其它字段 ,例如 (score+1) (name) 必须包含括号
                    $v = $v;
                } elseif (is_string($v)) {
                    $v = '"' . addslashes($v) . '"';
                }
                $res[$k] = $v;
            }
        }
        return $res;
    }

    /**
     * 执行查询，主要针对SELECT,SHOW等指令
     * @param string $sql sql指令
     * @return mixed
     */
    protected function _doQuery($sql = '')
    {
        $this->_lastSql = $sql;
        //prepare或者query返回一个PDOStat
        $pdostmt = self::$_db->prepare($this->_lastSql);
        $pdostmt->execute();
        $result = $pdostmt->fetchALL(PDO::FETCH_ASSOC);
        return $result;
    }

    /**
     * 执行语句，针对INSERT,UPDATE已经DELETE,exec结果返回受影响的行数
     * @param string $sql sql指令
     * @return mixed
     */
    protected function _doExec($sql = '')
    {
        $this->_lastSql = $sql;
        return self::$_db->exec($this->_lastSql);
    }

    /**
     * 执行sql语句，自动判断进行查询或者执行操作
     * @param string $sql sql指令
     * @return mixed
     */
    public function query($sql = '')
    {
        $queryIps = 'INSERT|UPDATE|DELETE|REPLACE|CREATE|DROP|LOAD DATA|SELECT .* INTO|COPY|ALTER|GRANT|REVOKE|LOCK|UNLOCK';
        if (preg_match('/^\s*"?(' . $queryIps . ')\s+/i', $sql)) {
            return $this->_doExec($sql);
        } else {
            //查询操作
            return $this->_doQuery($sql);
        }
    }

    /**
     * 获取最近一次查询的sql语句
     * @return String 执行的SQL
     */
    public function getLastSql()
    {
        return $this->_lastSql;
    }

    /**
     * 插入方法
     * @param array $data 字段->值 一维数组
     * @return int 受影响的行数
     * eg. insert(array('username'=>'zhangsan','age'=>30,'sex'=>'famale'))
     */
    public function insert(array $data)
    {
        $data = $this->_dataFormat($data);
        if (!$data) return;
        $sql = "insert into " . $this->_tbName . "(" . implode(',', array_keys($data)) . ") values(" . implode(',', array_values($data)) . ")";
        return $this->_doExec($sql);
    }

    /**
     * 删除方法
     * @return int 受影响的行数
     */
    public function delete()
    {
        //安全考虑，阻止全表删除
        if (!trim($this->_where)) return false;
        $sql = "delete from " . $this->_tbName . " " . $this->_where;
        $this->_clear = 1;
        $this->_clear();
        return $this->_doExec($sql);
    }

    /**
     * 更新操作
     * @param array $data 参数数组
     * @return int 受影响的行数
     * eg. where('id=3')->update(array('username'=>'zhangsan','age'=>30,'sex'=>'famale'))
     */
    public function update(array $data)
    {
        //安全考虑，阻止全表删除
        if (!trim($this->_where)) return false;
        $data = $this->_dataFormat($data);
        if (!$data) return;
        $sqlArr = array();
        foreach ($data as $k => $v) {
            $arr[] = $k . '=' . $v;
        }
        $sqlStr = implode(',', $sqlArr);
        $sql = "UPDATE " . trim($this->_tbName) . " SET " . trim($sqlStr) . " " . trim($this->_where);
        return $this->_doExec($sql);
    }

    /**
     * 查询全部记录(如果未带任何where条件)
     * @return array 结果集
     */
    public function select()
    {
        $sql = "SELECT " . trim($this->_field) . " FROM " . $this->_tbName . " " . trim($this->_where)
            . " " . trim($this->_order) . " " . trim($this->_limit);
        $this->_clear = 1;
        $this->_clear();
        return $this->_doQuery($sql);
    }

    /**
     * where条件拼接语句
     * @param $option 字符串或者数组
     * @return $this
     * eg. where('id>3') || where(array('id'=>3,'catid'=>11)) ||
     *     where(array('id'=>"1",'userid'=>array('12','>','or'))) => WHERE (`id`=1) or (`userid` > 12)
     */
    public function where($option)
    {
        if ($this->_clear > 0) $this->_clear();
        $this->_where = ' WHERE ';
        $logic = 'AND';
        if (is_string($option)) {
            $this->_where .= $option;
        } elseif (is_array($option)) {
            foreach ($option as $k => $v) {
                if (is_array($v)) {
                    $relative = isset($v[1]) ? $v[1] : '=';
                    $logic = isset($v[2]) ? $v[2] : 'and';
                    $condition = ' (' . $this->_addChar($k) . ' ' . $relative . ' ' . $v[0] . ') ';
                } else {
                    $logic = 'and';
                    $condition = ' (' . $this->_addChar($k) . '=' . $v . ') ';
                }
                $this->_where .= isset($mark) ? $logic . $condition : $condition;
                $mark = 1;
            }
        }
        return $this;
    }


    /**
     * 设置排序
     * @param $option 字符串或者数组
     * @return $this
     * eg. order('id asc') || order(array('id'=>'desc','catid'=>'asc'))
     */
    public function order($option)
    {
        if ($this->_clear > 0) $this->_clear();
        $this->_order = ' ORDER BY ';
        if (is_string($option)) {
            $this->_order .= $option;
        } elseif (is_array($option)) {
            foreach ($option as $key => $val) {
                $order = $this->_addChar($key) . ' ' . $val;
                $this->_order .= isset($mark) ? ',' . $order : $order;
                $mark = 1;
            }
        }
        return $this;
    }

    /**
     * 设置查询行数和页数
     * @param int $page pageSize不为空时为页数，否则为行数
     * @param int $pageSize 为空则函数设定取出行数，不为空则取出行数及页数
     * @return $this
     */
    public function limit($page, $pageSize = null)
    {
        if ($this->_clear > 0) $this->_clear();
        if ($pageSize == null) {
            $this->_limit = "LIMIT " . $page;
        } else {
            $pageval = intval(($page - 1) * $pageSize);
            $this->_limit = "LIMIT " . $pageval . ',' . $pageSize;
        }
        return $this;
    }

    /**
     * 设置查询字段
     * @param $field 字段字符串或者数组
     * @return $this 当前数据对象
     * eg. field('id,userid') || field(array('id','userid','username'))
     */
    public function field($field)
    {
        if ($this->_clear > 0) $this->_clear();
        if (is_string($field)) {
            $field = explode(',', $field);
        }
        $nField = array_map(array($this, '_addChar'), $field);
        $this->_field = implode(',', $nField);
        return $this;
    }

    /**
     * 清理标记函数
     */
    protected function _clear()
    {
        $this->_where = '';
        $this->_order = '';
        $this->_limit = '';
        $this->_field = '*';
        $this->_clear = 0;
    }

    /**
     * 手动清理标记
     */
    public function clearKey()
    {
        $this->_clear();
        return $this;
    }

    /**
     * 启动事务
     */
    public function startTrans()
    {
        if ($this->_trans == 0) self::$_db->beginTransaction();
        $this->_trans++;
        return;
    }

    /**
     * 用于非自动提交状态下面的查询提交
     * @return bool
     */
    public function commit()
    {
        $result = true;
        if ($this->_trans > 0) {
            $result = self::$_db->commit();
            $this->_trans = 0;
        }
        return $result;
    }

    /**
     * 事务回滚
     * @return bool
     */
    public function rollback()
    {
        $result = true;
        if ($this->_trans > 0) {
            $result = self::$_db->rollback();
            $this->_trans = 0;
        }
        return $result;
    }

    /**
     * 关闭连接
     * PHP在脚本结束时会自动关闭连接。
     */
    public function close()
    {
        if (!is_null(self::$_db)) self::$_db = null;
    }

}